package com.renli.mapper;

import com.renli.pojo.Employee;
import com.renli.utils.JDBCUtils;

import java.sql.*;
import java.sql.Date;
import java.util.*;

public class EmployeeMapper {
    /**
     * 添加新员工
     *
     * @param employee 员工信息
     * @return 添加是否成功
     * @throws SQLException 数据库操作异常
     */
    public static boolean add(Employee employee) throws SQLException {
        String sql = "INSERT INTO tb_employees (full_name, email, phone, department, position, join_date, salary, avatar) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        Object[] params = {
                employee.getFullName(),
                employee.getEmail(),
                employee.getPhone(),
                employee.getDepartment(),
                employee.getPosition(),
                employee.getJoinDate(), // 不需要转换为 java.sql.Date
                employee.getSalary(),
                employee.getAvatar()
        };
        return JDBCUtils.executeUpdate(sql, params) > 0;
    }

    /**
     * 删除员工 by 员工ID
     *
     * @param employeeId 员工ID
     * @return 删除是否成功
     * @throws SQLException 数据库操作异常
     */
    public static boolean delete(int employeeId) throws SQLException {
        String sql = "DELETE FROM tb_employees WHERE employee_id = ?";
        Object[] params = {employeeId};
        return JDBCUtils.executeUpdate(sql, params) > 0;
    }

    /**
     * 更新员工信息
     *
     * @param employee 员工信息
     * @return 更新是否成功
     * @throws SQLException 数据库操作异常
     */
    public static boolean update(Employee employee) throws SQLException {
        String sql = "UPDATE tb_employees SET full_name=?, email=?, phone=?, department=?, position=?, join_date=?, salary=?, avatar=? WHERE employee_id=?";
        Object[] params = {
                employee.getFullName(),
                employee.getEmail(),
                employee.getPhone(),
                employee.getDepartment(),
                employee.getPosition(),
                employee.getJoinDate(), // 不需要转换为 java.sql.Date
                employee.getSalary(),
                employee.getAvatar(),
                employee.getEmployeeId()
        };
        return JDBCUtils.executeUpdate(sql, params) > 0;
    }

    /**
     * 根据员工ID获取员工信息
     *
     * @param employeeId 员工ID
     * @return 员工信息
     * @throws SQLException 数据库操作异常
     */
    public static Employee getEmployeeById(int employeeId) throws SQLException {
        String sql = "SELECT * FROM tb_employees WHERE employee_id = ?";
        Object[] params = {employeeId};
        ResultSet resultSet = JDBCUtils.executeQuery(sql, params);
        if (resultSet.next()) {
            return resultSetToEmployee(resultSet);
        }
        return null; // 返回null表示未找到员工
    }

    /**
     * 获取所有员工的记录总数
     *
     * @return 记录总数
     * @throws SQLException 数据库操作异常
     */
    public static int getTotalEmployeeCount() throws SQLException {
        String sql = "SELECT COUNT(*) FROM tb_employees";
        ResultSet resultSet = JDBCUtils.executeQuery(sql);
        if (resultSet.next()) {
            return resultSet.getInt(1);
        }
        return 0;
    }

    /**
     * 分页查询员工列表（根据员工全名模糊搜索）
     *
     * @param fullName 员工全名（可选，可以为null或空字符串）
     * @param pageNum  当前页码（从1开始）
     * @param pageSize 每页记录数
     * @return 员工列表
     * @throws SQLException 数据库操作异常
     */
    public static List<Employee> getEmployeesByPage(String fullName, int pageNum, int pageSize) throws SQLException {
        List<Employee> employees = new ArrayList<>();
        int startIndex = (pageNum - 1) * pageSize; // 计算起始索引
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM tb_employees");

        // 构建WHERE子句以实现模糊搜索
        if (fullName != null && !fullName.isEmpty()) {
            sqlBuilder.append(" WHERE full_name LIKE ?");
        }

        sqlBuilder.append(" LIMIT ?, ?");

        Object[] params;
        if (fullName != null && !fullName.isEmpty()) {
            params = new Object[]{"%" + fullName + "%", startIndex, pageSize};
        } else {
            params = new Object[]{startIndex, pageSize};
        }

        ResultSet resultSet = JDBCUtils.executeQuery(sqlBuilder.toString(), params);
        while (resultSet.next()) {
            employees.add(resultSetToEmployee(resultSet));
        }

        return employees;
    }

    /**
     * 将ResultSet中的数据转换为Employee对象
     *
     * @param resultSet ResultSet对象
     * @return Employee对象
     * @throws SQLException 数据库操作异常
     */
    private static Employee resultSetToEmployee(ResultSet resultSet) throws SQLException {
        Employee employee = new Employee();
        employee.setEmployeeId(resultSet.getInt("employee_id"));
        employee.setFullName(resultSet.getString("full_name"));
        employee.setEmail(resultSet.getString("email"));
        employee.setPhone(resultSet.getString("phone"));
        employee.setDepartment(resultSet.getString("department"));
        employee.setPosition(resultSet.getString("position"));
        Timestamp joinDateTimestamp = resultSet.getTimestamp("join_date");
        employee.setJoinDate(joinDateTimestamp);
        employee.setSalary(resultSet.getDouble("salary"));
        employee.setAvatar(resultSet.getString("avatar"));
        return employee;
    }

    /**
     * 批量删除员工 by 员工ID列表
     *
     * @param employeeIds 员工ID列表
     * @return 删除是否成功
     * @throws SQLException 数据库操作异常
     */
    public static boolean deleteBatch(List<Integer> employeeIds) throws SQLException {
        if (employeeIds.isEmpty()) {
            return false;
        }

        StringBuilder sql = new StringBuilder("DELETE FROM tb_employees WHERE employee_id IN (");
        for (int i = 0; i < employeeIds.size(); i++) {
            sql.append("?");
            if (i < employeeIds.size() - 1) {
                sql.append(",");
            }
        }
        sql.append(")");

        Object[] params = employeeIds.toArray();

        return JDBCUtils.executeUpdate(sql.toString(), params) > 0;
    }


    /**
     * 查询各部门入职人数
     *
     * @return 部门及入职人数的映射
     * @throws SQLException 数据库操作异常
     */
    public static List<Map<String, Object>> getEntryCountsByDepartment() throws SQLException {
        List<Map<String, Object>> entryCounts = new ArrayList<>();
        String sql = "SELECT department, COUNT(*) AS entry_count FROM tb_employees GROUP BY department ORDER BY entry_count DESC";
        ResultSet resultSet = JDBCUtils.executeQuery(sql);

        while (resultSet.next()) {
            String department = resultSet.getString("department");
            int count = resultSet.getInt("entry_count");

            // 使用HashMap构建部门及入职人数的映射
            Map<String, Object> entryCountMap = new HashMap<>();
            entryCountMap.put("department", department);
            entryCountMap.put("entry_count", count);

            entryCounts.add(entryCountMap);
        }

        return entryCounts;
    }

}



